﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace winform
{
    /// <summary>
    /// Acess数据库连接执行一体化小工厂
    /// 作者：马礼慧
    /// 时间：2010 年 8 月8 号 
    /// </summary>
    class DbHelperOleDb
    {
        public static string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBkd.mdb;Jet OLEDB:Database Password=wzzx@wzmc ;";
        //public static string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBkd.mdb ;Persist Security Info=TRUE;";
        public static OleDbConnection Conn = new OleDbConnection(ConnectionString);

        #region 连接和关闭
        public void OpenConn()
        {
            try
            {
                if (Conn.State != ConnectionState.Open)
                {
                    Conn.Open();
                }
            }
            catch (OleDbException e)
            {
                System.Windows.Forms.MessageBox.Show(Conn.State.ToString());
                MessageBox.Show("无法建立数据库联接,原因：" + e.Message);
            }
        }

        public void CloseConn()
        {
            try
            {
                Conn.Close();
            }
            catch (OleDbException e)
            {
                MessageBox.Show("无法建立数据库联接,原因：" + e.Message);
            }
        }
        #endregion

        #region 执行sql语句

        /// <summary>
        /// 查询方法（返回dt)
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static DataTable ExecuteWithDataTable(string cmdText, OleDbParameter[] cmdParms)
        {
              DataSet ds = new DataSet();
              try
              {
                  OleDbCommand cmd = new OleDbCommand();
                  PrepareCommand(cmd, Conn, null, CommandType.Text, cmdText, cmdParms);
                  //cmd1.ExecuteScalar();//用于添加
                  OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                  ad.Fill(ds, "tempTable");
                  cmd.Parameters.Clear();
              }
              catch (OleDbException ey)
              {
                  Conn.Close();
                  throw new Exception("查询序列的时候出错！原因：" + ey.Message);
              }
              return ds.Tables["tempTable"];
              
        }

        /// <summary>
        /// 查询方法（返回[][])
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static string[][] ExecuteWithArray(string cmdText, OleDbParameter[] cmdParms)
        {
            DataSet ds = new DataSet();
            try
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, Conn, null, CommandType.Text, cmdText, cmdParms);
                //cmd1.ExecuteScalar();//用于添加
                OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
                ad.Fill(ds, "tempTable");
                cmd.Parameters.Clear();
            }
            catch (OleDbException ey)
            {
                Conn.Close();
                throw new Exception("查询序列的时候出错！原因：" + ey.Message);
            }
            
            string[][] Result=convertTableToArray(ds.Tables["tempTable"]);
            return Result;

        }

        /// <summary>
        /// 执行（添加和更新记录）
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static int ExecuteSql(string cmdText, OleDbParameter[] cmdParms)
        {
            try
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, Conn, null, CommandType.Text, cmdText,cmdParms);
                int i=cmd.ExecuteNonQuery();
                return i;
            }
            catch (OleDbException ey)
            {
                Conn.Close();
                throw new Exception("执行的时候出错！原因：" + ey.Message);
            }
        }

        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection Conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
        {
            //Open the Connection if required
            if (Conn.State != ConnectionState.Open)
            {
                try
                {
                    Conn.Open();
                }
                catch (OleDbException e) //OleDbException
                {
                    string em = "";
                    for (int i = 0; i < e.Errors.Count; i++)
                        em += e.Errors[i].Message;
                    MessageBox.Show("无法建立数据库联接,原因： " + Conn.State + Conn.ConnectionString + e.ErrorCode);
                }
            }

            //Set up the command
            try
            {
                cmd.Connection = Conn;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;

                //Bind it to the transaction if it exists
                if (trans != null)
                    cmd.Transaction = trans;

                // Bind the parameters passed in
                if (cmdParms != null)
                {
                    foreach (OleDbParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }
            catch (Exception ee)
            {
                throw new Exception(ee.Message);
            }

        }

        /// <summary>
        /// 将DataTable转换为交错数组数组（WCF不支持多为数组）
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static string[][] convertTableToArray(DataTable dataTable)
        {

            string[][] result;
            string[] resultColumn;
            DataRow tmpRow;
            int i;
            int j;
            int rowCount;
            int cloumnCount;
            if ((dataTable != null) && dataTable.Rows.Count > 0 && dataTable.Columns.Count > 0)
            {
                rowCount = dataTable.Rows.Count;
                cloumnCount = dataTable.Columns.Count;
                result = new string[dataTable.Rows.Count][];

                for (i = 0; i < rowCount; i++)
                {
                    resultColumn = new string[dataTable.Columns.Count];
                    tmpRow = dataTable.Rows[i];
                    for (j = 0; j < cloumnCount; j++)
                    {
                        if (!(tmpRow[j] is DBNull))
                        {
                            //result[i, j] =  tmpRow[j].ToString() ;
                            resultColumn[j] = tmpRow[j].ToString();
                        }
                        else
                        {
                            //result[i, j] = "";
                            resultColumn[j] = "";
                        }
                        result[i] = resultColumn;
                    }
                }
                return result;
            }
            else
            {
                return null;
            }
        }

        #endregion
    }
}
